/*
	
*/
ALTER PROC SP_INSERT_LOT_FOR_PO_RECEIPTS @RECEIPT_LINEITEM_ID INT,
					@LOT_ID INT,
					@LOT_NUMBER VARCHAR(50),
					@QTY_IN INT,
					@VENDOR_ID INT,
					@PROCESSSTEP_ID INT,
					@PO_NUMBER VARCHAR(50)
AS
BEGIN
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @LOCATION_ID INT
	DECLARE @LOT_CURRENT_STATUS VARCHAR(50)
	DECLARE @RECEIPT_ID INT
	DECLARE @TOTAL_QTY_IN INT
	DECLARE @TOTAL_QTY_PULLED INT

	SELECT @RECEIPT_ID = RECEIPT_ID FROM PURCHASEORDER_RECEIPT_LINEITEM_TABLE WHERE RECEIPT_LINEITEM_ID = @RECEIPT_LINEITEM_ID

	IF ( @LOT_ID IS NOT NULL AND @LOT_ID > 0 )
	BEGIN
		INSERT INTO PURCHASEORDER_RECEIPT_LOT_TABLE( RECEIPT_LINEITEM_ID, LOT_NUMBER, QTY_IN, QTY_OUT, LOT_ID )
					VALUES( @RECEIPT_LINEITEM_ID, @LOT_NUMBER, @QTY_IN, @QTY_IN, @LOT_ID )
	END
	ELSE --NEED TO CREATE LOT WITH CLOSED STATUS
	BEGIN
		SET @LOT_ID = NULL
		SELECT @PS_TYPE_NAME = PS_TYPE_NAME FROM PROCESS_STEP WHERE PROCESSSTEP_ID = @PROCESSSTEP_ID
		SELECT @LOCATION_ID = LOCATION_ID FROM LOCATION WHERE VENDOR_ID = @VENDOR_ID AND L_TYPE = @PS_TYPE_NAME
		SELECT @LOT_ID = LOT_ID, @LOT_CURRENT_STATUS = LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_NUMBER = @LOT_NUMBER AND LOCATION_ID = @LOCATION_ID AND PROCESSSTEP_ID = @PROCESSSTEP_ID
		IF ( @LOT_ID IS NULL )
		BEGIN
			IF @PS_TYPE_NAME = 'FAB'
			BEGIN
				INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, WAFER_QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT ) 
					VALUES (@PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', '', '', @PO_NUMBER, '', 0 )
				SELECT @LOT_ID = @@IDENTITY
		
				INSERT INTO FAB_LOT(LOT_ID, WAFER_ID, TIMESTAMP) VALUES (@LOT_ID, NULL, CURRENT_TIMESTAMP )
				--COMMENTED AS A JOB WILL UPDATE THE HISTORY TABLE
				/*INSERT INTO LOT_TABLE_HISTORY (LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, WAFER_QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT) 
						VALUES ( @LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', @PO_NUMBER, '', 0 )*/
			END			
			ELSE IF ( @PS_TYPE_NAME = 'ASSEMBLY' )
			BEGIN
				INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT ) 
					VALUES (@PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', '', '', @PO_NUMBER, '', 0 )
				SELECT @LOT_ID = @@IDENTITY
				
				INSERT INTO Assembly_Lot( LOT_ID, TIMESTAMP) VALUES (@LOT_ID, CURRENT_TIMESTAMP)
				--COMMENTED AS A JOB WILL UPDATE THE HISTORY TABLE
				/*INSERT INTO LOT_TABLE_HISTORY (LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT) 
						VALUES ( @LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', @PO_NUMBER, '', 0 )*/
			END
			ELSE IF ( @PS_TYPE_NAME = 'TEST' )
			BEGIN
				INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT ) 
					VALUES (@PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', '', '', @PO_NUMBER, '', 0 )
				SELECT @LOT_ID = @@IDENTITY
				
				INSERT INTO FINAL_TEST_LOT( LOT_ID, TIMESTAMP) VALUES (@LOT_ID, CURRENT_TIMESTAMP)
				--COMMENTED AS A JOB WILL UPDATE THE HISTORY TABLE
				/*INSERT INTO LOT_TABLE_HISTORY (LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, QTY_IN, QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT) 
						VALUES ( @LOT_ID, @PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', @PO_NUMBER, '', 0 )*/
			END
			ELSE IF ( @PS_TYPE_NAME = 'SORT' )
			BEGIN
				INSERT INTO LOT_TABLE (PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, FAB_LOT_NUMBER, VENDOR_LOT_NUMBER, WAFER_QTY_IN, WAFER_QTY_OUT, DATE_IN, DATE_OUT,LOT_CURRENT_STATUS, TIMESTAMP,LOT_ENTERED_BY, LOT_CATEGORY, LOT_DATE_CODE, LOT_COMMENTS, PO_NUMBER, SHOP_ORDER_NUMBER, LOT_DROP_OUT ) 
					VALUES (@PROCESSSTEP_ID, @LOCATION_ID, @LOT_NUMBER, @LOT_NUMBER, @LOT_NUMBER, @QTY_IN, @QTY_IN, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Close', CURRENT_TIMESTAMP, 0, 'PROD', '', '', @PO_NUMBER, '', 0 )
				SELECT @LOT_ID = @@IDENTITY

				INSERT INTO SORT_LOT(LOT_ID, [TIMESTAMP]) VALUES (@LOT_ID, CURRENT_TIMESTAMP )
			END
		 END
		/*ELSE --LOT ALREADY EXISTS
		 BEGIN
		 END*/
		INSERT INTO PURCHASEORDER_RECEIPT_LOT_TABLE( RECEIPT_LINEITEM_ID, LOT_NUMBER, QTY_IN, QTY_OUT, LOT_ID )
			VALUES( @RECEIPT_LINEITEM_ID, @LOT_NUMBER, @QTY_IN, @QTY_IN, @LOT_ID )		
 	END
	UPDATE LOT_TABLE SET PO_NUMBER = @PO_NUMBER WHERE LOT_ID = @LOT_ID
	
	-- TO UPDATE THE PO RECEIPT ID WHEN THE LOT IS COMPLETLY RECEIVED
	SELECT @TOTAL_QTY_IN = ISNULL( QTY_IN, WAFER_QTY_IN ) FROM LOT_TABLE WHERE LOT_ID = @LOT_ID
	SELECT @TOTAL_QTY_PULLED = ISNULL( SUM( ISNULL( QTY_IN, 0 ) ), 0 ) FROM PURCHASEORDER_RECEIPT_LOT_TABLE WHERE Lot_ID = @LOT_ID

	IF( @TOTAL_QTY_IN <= @TOTAL_QTY_PULLED )
	BEGIN
		UPDATE LOT_TABLE SET PO_RECEIPT_ID = @RECEIPT_ID WHERE LOT_ID = @LOT_ID		
	END
END